package com.asistencia.dao.jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.asistencia.dao.interfaces.AssistanceDao;
import com.asistencia.domain.Assistance;
import com.asistencia.domain.Enrollment;
import com.asistencia.domain.Schedule;




@Repository
@SuppressWarnings({ "rawtypes", "unchecked" })
public class AssistanceJdbc implements AssistanceDao {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Override
	public int insert(Assistance ass) throws DataAccessException {
		String SQL = "INSERT INTO assistance VALUES (?,?,?,?,?,?)";
		int result;
		result = jdbcTemplate.update(SQL, new Object[] { null,ass.getAssistanceDate(),ass.getAssistanceTime(), ass.getAction(),	ass.getMyEnrollment().getIdEnrollment(),ass.getMySchedule().getIdSchedule()});
		return result;	
	}
	

	@Override
	public int update(Assistance ass) throws DataAccessException {
		String SQL = "UPDATE assistance SET action=? , assistance_hour=? WHERE idassistance = ?";
		int result;
		result = jdbcTemplate.update(SQL, new Object[] {ass.getAction(),ass.getAssistanceTime(),ass.getIdAsistence()});
		return result;
	}

	@Override
	public Assistance getByScheduleEnrollment(Integer idSchedule, Integer idEnrollment) throws DataAccessException {
		String SQL="SELECT * FROM assistance WHERE idschedule =? AND idenrollment = ? LIMIT 1";
		List matches = jdbcTemplate.query(SQL, new Object[] { idSchedule,idEnrollment }, new RowMapper() {
			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				Assistance assistance= new Assistance();
				Enrollment enrollment= new Enrollment();
				Schedule schedule =new Schedule();
				
				assistance.setIdAsistence(rs.getInt("idassistance"));
				assistance.setAssistanceDate(rs.getString("assistance_date"));
				assistance.setAssistanceTime(rs.getString("assistance_hour"));
				assistance.setAction(rs.getString("action"));
				enrollment.setIdEnrollment(rs.getInt("idenrollment"));
				schedule.setIdSchedule(rs.getInt("idschedule"));
				assistance.setMyEnrollment(enrollment);
				assistance.setMySchedule(schedule);
				return assistance;
			}
			});
		return (Assistance) (matches.size() > 0 ? (Assistance) matches.get(0) : null);
		}
	
@Override
	public Assistance getById(Integer idAssistance) throws DataAccessException {
		String SQL="SELECT * FROM assistance WHERE idassistance=? LIMIT 1";
		List matches = jdbcTemplate.query(SQL, new Object[] { idAssistance }, new RowMapper() {
			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				Assistance assistance= new Assistance();
				Enrollment enrollment= new Enrollment();
				Schedule schedule =new Schedule();
				
				assistance.setIdAsistence(rs.getInt("idassistance"));
				assistance.setAssistanceDate(rs.getString("assistance_date"));
				assistance.setAssistanceTime(rs.getString("assistance_hour"));
				assistance.setAction(rs.getString("action"));
				enrollment.setIdEnrollment(rs.getInt("idenrollment"));
				schedule.setIdSchedule(rs.getInt("idschedule"));
				assistance.setMyEnrollment(enrollment);
				assistance.setMySchedule(schedule);
				return assistance;
			}
		});

		return (matches.size() > 0 ? (Assistance) matches.get(0) : null);
	}
	

		
	@Override
	public List<Assistance> lisByEnrollment(Integer idStudent) throws DataAccessException {
		String SQL="SELECT * FROM assistance WHERE idenrollment =?";
		List matches = jdbcTemplate.query(SQL, new Object[] { idStudent }, new RowMapper() {
			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				Assistance assistance= new Assistance();
				Enrollment enrollment= new Enrollment();
				Schedule schedule =new Schedule();
				
				assistance.setIdAsistence(rs.getInt("idassistance"));
				assistance.setAssistanceDate(rs.getString("assistance_date"));
				assistance.setAssistanceTime(rs.getString("assistance_hour"));
				assistance.setAction(rs.getString("action"));
				enrollment.setIdEnrollment(rs.getInt("idenrollment"));
				schedule.setIdSchedule(rs.getInt("idschedule"));
				assistance.setMyEnrollment(enrollment);
				assistance.setMySchedule(schedule);
				return assistance;
			}
		});

		return matches.size() > 0 ? (List<Assistance>)matches:null;
	}

	@Override
	public List<Assistance> lisById(int idStudent) throws DataAccessException {
		String SQL ="SELECT * FROM assistance WHERE idenrollment =? ";
		List matches = jdbcTemplate.query(SQL, new Object[] { idStudent }, new RowMapper() {
			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				Assistance assistance= new Assistance();
				Enrollment enrollment= new Enrollment();
				Schedule schedule =new Schedule();
				
				assistance.setIdAsistence(rs.getInt("idassistance"));
				assistance.setAssistanceDate(rs.getString("assistance_date"));
				assistance.setAssistanceTime(rs.getString("assistance_hour"));
				assistance.setAction(rs.getString("action"));
				enrollment.setIdEnrollment(rs.getInt("idenrollment"));
				schedule.setIdSchedule(rs.getInt("idschedule"));
				assistance.setMyEnrollment(enrollment);
				assistance.setMySchedule(schedule);
				return assistance;
			}
		});

		return matches.size() > 0 ? (List<Assistance>)matches:null;
	}
}
	
